REM  SQL scripts for Chapter 7 of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.


alter rollback segment SEGMENT_NAME offline;

drop rollback segment SEGMENT_NAME;

create rollback segment SEGMENT_NAME
tablespace RBS;


alter rollback segment SEGMENT_NAME online;

commit;

set transaction use rollback segment ROLL_BATCH
insert into TABLE_NAME
select * from DATA_LOAD_TABLE;

commit;

REM*  The commit command clears the rollback segment assignment.
REM*  Implicit commits, like those caused by DDL commands, will
REM*  also clear the rollback segment designation.

insert into TABLE_NAME select * from SOME_OTHER_TABLE;



select
     N.Name,             /* rollback segment name */
     S.OptSize           /* rollback segment OPTIMAL size */
from V$ROLLNAME N, V$ROLLSTAT S
where N.USN=S.USN;

alter rollback segment R1 shrink to 15M;

alter rollback segment R1 shrink;


DROP TABLE stats$begin_roll;
CREATE TABLE stats$begin_roll
AS SELECT * FROM v$rollstat WHERE 0 = 1;

DROP TABLE stats$end_roll;
CREATE TABLE stats$end_roll
AS SELECT * FROM stats$begin_roll;

INSERT INTO stats$begin_roll SELECT * FROM v$rollstat;


INSERT INTO stats$end_roll SELECT * FROM v$rollstat;

CREATE TABLE stats$roll
AS SELECT  e.usn undo_segment,
        e.gets-b.gets trans_tbl_gets,
   e.waits-b.waits trans_tbl_waits,
   e.writes-b.writes undo_bytes_written,
   e.rssize segment_size_bytes,
        e.xacts-b.xacts xacts,
   e.shrinks-b.shrinks shrinks,
        e.wraps-b.wraps wraps
   FROM stats$begin_roll b, stats$end_roll e
        WHERE e.usn = b.usn;


select
   N.Name,                      /* rollback segment name */
   S.RsSize                     /* rollback segment size */
from V$ROLLNAME N, V$ROLLSTAT S
where N.USN=S.USN;


REM  Users in rollback segments
REM
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
select R.Name rr,
       nvl(S.Username,'no transaction') us,
       S.Osuser os,
       S.Terminal te
  from V$LOCK L, V$SESSION S, V$ROLLNAME R
 where L.Sid = S.Sid(+)
   and trunc(L.Id1/65536) = R.USN
   and L.Type = 'TX'
   and L.Lmode = 6
order by R.Name
/

select
   N.Name,                      /* rollback segment name */
   S.Writes                     /* bytes written to date */
from V$ROLLNAME N, V$ROLLSTAT S
where N.USN=S.USN;

set transaction use rollback segment SEGMENT_NAME


alter tablespace RBS
default storage
(initial 125K next 125K minextents 18 maxextents 249)


create rollback segment R4 tablespace RBS
   storage (optimal 2250K);
alter rollback segment R4 online;

set transaction use rollback segment SEGMENT_NAME



